# backend/db.py
import logging
import json
import redis
from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector import Error

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")
logger = logging.getLogger("db")

# MySQL 配置（你给的）
DB_CONFIG = {
    "host": "192.168.2.38",
    "port": 3306,
    "user": "root",
    "password": "123mmm",
    "database": "fc",
    "charset": "utf8mb4"
}

connection_pool = MySQLConnectionPool(
    pool_name="chat_pool",
    pool_size=5,
    pool_reset_session=True,
    **DB_CONFIG
)

# Redis client (binary-safe)
redis_client = redis.Redis(host="localhost", port=6379, db=0, decode_responses=False)

def init_db():
    cnx = None
    cursor = None
    try:
        cnx = connection_pool.get_connection()
        cursor = cnx.cursor()
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS history (
                id INT NOT NULL AUTO_INCREMENT,
                conv_id INT NOT NULL,
                role VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                audio_path VARCHAR(1024),
                duration FLOAT,
                segment_index INT,
                PRIMARY KEY (id),
                INDEX idx_conv_id (conv_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
        )
        logger.info("✅ history table ready")
    except Error as err:
        logger.exception(f"Error init DB: {err}")
    finally:
        if cursor:
            cursor.close()
        if cnx:
            cnx.close()
